/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.scs.ubbcluj.view;

import edu.scs.ubbcluj.backend.Join;
import edu.scs.ubbcluj.backend.MongoDB;
import edu.scs.ubbcluj.backend.Selection;
import edu.scs.ubbcluj.model.Attribute;
import edu.scs.ubbcluj.model.Condition;
import edu.scs.ubbcluj.model.Database;
import edu.scs.ubbcluj.model.Databases;
import edu.scs.ubbcluj.model.ForeignKey;
import edu.scs.ubbcluj.model.Model;
import edu.scs.ubbcluj.model.Table;
import java.util.ArrayList;
import java.util.List;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.Event;
import javafx.event.EventHandler;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.CheckBox;
import javafx.scene.control.ComboBox;
import javafx.scene.control.ContextMenu;
import javafx.scene.control.Label;
import javafx.scene.control.MenuItem;
import javafx.scene.control.RadioButton;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.ToggleGroup;
import javafx.scene.control.TreeCell;
import javafx.scene.control.TreeItem;
import javafx.scene.image.Image;
import javafx.scene.image.ImageView;
import javafx.scene.layout.GridPane;
import javafx.scene.layout.VBox;
import javafx.stage.Modality;
import javafx.stage.Stage;

/**
 *
 * @author Torok
 */
public class AddItem extends TreeCell {

    private TextField textField;
    private final Image dbIcon = new Image(getClass().getResourceAsStream("root1.png"));
    private final Image tableIcon
            = new Image(getClass().getResourceAsStream("tableicon_1.png"));
    private final Image attrIcon
            = new Image(getClass().getResourceAsStream("dot_1.jpg"));
    private ContextMenu dbsMenu = new ContextMenu();
    private ContextMenu dbMenu = new ContextMenu();
    private ContextMenu tableMenu = new ContextMenu();
    private ContextMenu attrMenu = new ContextMenu();
    private Databases dbs;
    private static final Short ORD = 1;

    public AddItem(Databases dbs) {
        this.dbs = dbs;
        createDBSMenu();
        createDBMenu();
        createTableMenu();
        createAttrMenu();
        MongoDB.connectToMongo();
    }

    private void createDBSMenu() {
        MenuItem addDBItem = new MenuItem("Add DataBase");
        dbsMenu.getItems().add(addDBItem);
        addDBItem.setOnAction(new EventHandler() {
            public void handle(Event t) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                VBox dialogVbox = new VBox(20);
                dialogVbox.getChildren().add(new Label("Database name:"));
                final TextField dbName = new TextField();
                dbName.setMaxWidth(90);
                dialogVbox.getChildren().add(dbName);
                Button saveButton = new Button("Done");
                dialogVbox.getChildren().add(saveButton);
                Scene dialogScene = new Scene(dialogVbox, 200, 100);
                dialog.setScene(dialogScene);
                dialog.show();
                saveButton.setOnAction(new EventHandler() {

                    @Override
                    public void handle(Event t) {
                        Database db = new Database(dbName.getText());
                        TreeItem dbItem
                                = new TreeItem(db, new ImageView(dbIcon));
                        dbs.addDatabase(db);
                        getTreeItem().getChildren().add(dbItem);
                        dialog.close();
                    }

                });

            }
        });
    }

    private void createDBMenu() {

        MenuItem addTableItem = new MenuItem("Add Table");
        MenuItem delDBItem = new MenuItem("Delete DataBase");
        MenuItem joinItem = new MenuItem("Join tables");
        dbMenu.getItems().add(addTableItem);
        dbMenu.getItems().add(delDBItem);
        dbMenu.getItems().add(joinItem);
        addTableItem.setOnAction(new EventHandler() {
            public void handle(Event t) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                VBox dialogVbox = new VBox(20);
                dialogVbox.getChildren().add(new Label("Table name:"));
                final TextField tableName = new TextField();
                tableName.setMaxWidth(90);
                dialogVbox.getChildren().add(tableName);
                Button saveButton = new Button("Done");
                dialogVbox.getChildren().add(saveButton);
                Scene dialogScene = new Scene(dialogVbox, 200, 100);
                dialog.setScene(dialogScene);
                dialog.show();
                saveButton.setOnAction(new EventHandler() {

                    @Override
                    public void handle(Event t) {
                        Table table = new Table(tableName.getText());
                        TreeItem tableItem
                                = new TreeItem(table, new ImageView(tableIcon));
                        Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getValue().toString());
                        db.addTable(table);
                        MongoDB.setDatastore(db);
                        MongoDB.createCollection(table);
                        getTreeItem().getChildren().add(tableItem);
                        dialog.close();
                    }

                });

            }
        });
        delDBItem.setOnAction(new EventHandler() {

            @Override
            public void handle(Event t) {
                Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getValue().toString());
                dbs.removeDatabase(db);
                MongoDB.setDatastore(db);
                MongoDB.deleteDB(db);
                getTreeItem().getParent().getChildren().remove(getTreeItem());
            }
        });
        joinItem.setOnAction(new EventHandler() {

            @Override
            public void handle(Event t) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                final VBox dialogVbox = new VBox(20);
                final Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getValue().toString());
                MongoDB.setDatastore(db);
                
                GridPane grid = new GridPane();
                grid.setPadding(new Insets(5));
                grid.setHgap(5);
                grid.setVgap(5);
                
                final List<CheckBox> tablesForJoin = new ArrayList<>();
                final List<ComboBox> attrsForJoin = new ArrayList<>();
                
                int i = 0;
                grid.add(new Label("tables for join:"), 0, i);
                grid.add(new Label("attributes for join:"), 1, i);
                i++;
                
                for (Table tab: db.getTables()){
                    CheckBox cb = new CheckBox(tab.getName());
                    tablesForJoin.add(cb);
                    grid.add(cb, 0, i);
                    
                    final ComboBox attrCombo = new ComboBox();
                    List<String> list = new ArrayList<>();
                    for(Attribute attr:tab.getPrimaryKeys())
                        list.add(attr.getName());
                    for(Attribute attr:tab.getAttributes())
                        list.add(attr.getName());
                    for(Attribute attr:tab.getForeignKeys())
                        list.add(attr.getName());
                    ObservableList<String> observableList = FXCollections.observableList(list);
                    attrCombo.setItems(observableList);
                    attrsForJoin.add(attrCombo);
                    grid.add(attrCombo, 1, i);

                    i++;
                }
                
                dialogVbox.getChildren().add(grid);
                
                final CheckBox nested = new CheckBox("indexed nested loop (default:merge)");
                dialogVbox.getChildren().add(nested);
                    
                Button joinButton = new Button("Join");
                dialogVbox.getChildren().add(joinButton);
                
                Scene dialogScene = new Scene(dialogVbox, 600, 400);
                dialog.setScene(dialogScene);
                dialog.show();
                
                joinButton.setOnAction(new EventHandler() {
                    @Override
                    public void handle(Event t) {
                        List<Table> wantedTables = new ArrayList<>();
                        List<Attribute> wantedAttrs = new ArrayList<>();
                        for(int i=0; i<tablesForJoin.size(); i++)
                            if(tablesForJoin.get(i).isSelected() && attrsForJoin.get(i).getValue()!=null) {
                                Table tab = Util.getTableByName(db.getTables(), tablesForJoin.get(i).getText());
                                wantedTables.add(tab);
                                wantedAttrs.add(Util.getAttributeByName(tab, attrsForJoin.get(i).getValue().toString()));
                            }
                        Table table1 = wantedTables.get(0);
                        Attribute join1 = wantedAttrs.get(0);
                        Table table2 = wantedTables.get(1);
                        Attribute join2 = wantedAttrs.get(1);
                        List<List<String>> values = null;
                        List<Attribute> resultAttrsName = null;
                        
                        if(nested.isSelected()){
                            Table table1Original = wantedTables.get(0);
                            Table table2Original = wantedTables.get(1);
                                values = Join.indexedNestedLoop(table1, table1Original, join1, table2, table2Original, join2);
                            resultAttrsName = table1.getAllAttributes();
                            resultAttrsName.remove(join1);
                            Attribute next = new Attribute("join", join1.getTip(), join1.getLength(), join1.isIsNull());
                            List<Attribute> resultAttrsNameHelper = new ArrayList<Attribute>();
                            resultAttrsNameHelper.addAll(table2.getAllAttributes());
                            resultAttrsNameHelper.set(resultAttrsNameHelper.indexOf(join2),next);
                            resultAttrsName.addAll(resultAttrsNameHelper);
                            for(int i=2; i<wantedTables.size(); i++) {
                                if(values == null)
                                    break;
                                table1Original = table1;
                                table2Original = wantedTables.get(i);
                                
                                //table1: az elozo ket tabla joinja, kiirva
                                table1 = new Table("tempJoin");
                                table1.setAttributes(resultAttrsName);
                                MongoDB.setDatastore(db);
                                MongoDB.createCollection(table1);
                                for (List<String> value : values) {
                                    MongoDB.saveSpecCollection(table1, value);
                                }
                                
                                join1 = next;
                                table2 = wantedTables.get(i);
                                join2 = wantedAttrs.get(i);
                                values = Join.indexedNestedLoop(table1, table1Original, join1, table2, table2Original, join2);
                                MongoDB.deleteCollection(table1);
                                
                                resultAttrsName = table1.getAllAttributes();
                                resultAttrsName.remove(join1);
                                next = new Attribute("join", join1.getTip(), join1.getLength(), join1.isIsNull());
                                resultAttrsNameHelper = new ArrayList<Attribute>();
                                resultAttrsNameHelper.addAll(table2.getAllAttributes());
                                resultAttrsNameHelper.set(resultAttrsNameHelper.indexOf(join2),next);
                                resultAttrsName.addAll(resultAttrsNameHelper);                            
                            }
                        } else {
                            values = Join.mergeJoin(table1, join1, table2, join2);
                            resultAttrsName = table1.getAllAttributes();
                            resultAttrsName.remove(join1);
                            Attribute next = new Attribute("join", join1.getTip(), join1.getLength(), join1.isIsNull());
                            List<Attribute> resultAttrsNameHelper = new ArrayList<Attribute>();
                            resultAttrsNameHelper.addAll(table2.getAllAttributes());
                            resultAttrsNameHelper.set(resultAttrsNameHelper.indexOf(join2),next);
                            resultAttrsName.addAll(resultAttrsNameHelper);
                            for(int i=2; i<wantedTables.size(); i++) {
                                table1 = new Table("temp");
                                table1.setAttributes(resultAttrsName);
                                join1 = next;
                                table2 = wantedTables.get(i);
                                join2 = wantedAttrs.get(i);
                                values = Join.mergeJoin(values,table1, join1, table2, join2);
                                resultAttrsName = table1.getAllAttributes();
                                resultAttrsName.remove(join1);
                                next = new Attribute("join", join1.getTip(), join1.getLength(), join1.isIsNull());
                                resultAttrsNameHelper = new ArrayList<Attribute>();
                                resultAttrsNameHelper.addAll(table2.getAllAttributes());
                                resultAttrsNameHelper.set(resultAttrsNameHelper.indexOf(join2),next);
                                resultAttrsName.addAll(resultAttrsNameHelper);
                            
                            }
                        /*System.out.println("Values from group by");
                        System.out.println(values.size());
                        System.out.println(values.get(0).size());
                        for(int k=0; k<values.size(); k++){
                            for(int j=0; j<values.get(k).size(); j++)
                                System.out.print(values.get(k).get(j));
                            System.out.println();
                        for(int k=0; k<resultAttrsName.size(); k++){
                                System.out.println(resultAttrsName.get(k));
                        }
                        }*/
                        }
                                                
                        if (dialogVbox.getChildren().size() > 3) {
                            dialogVbox.getChildren().remove(3);
                        }
                        if(values != null)
                            dialogVbox.getChildren().add(3, DBTableView.createTable(resultAttrsName, values, null));
                        else 
                            errorBox("Can not reproduce!");
                    }

                });  
            }
         });      
    }

    private void createTableMenu() {
        tableMenu = new ContextMenu();
        MenuItem addAttrItem = new MenuItem("Add Attribute");
        MenuItem addRefItem = new MenuItem("Add Foreign Key");
        MenuItem delTableItem = new MenuItem("Delete Table");
        MenuItem insertItem = new MenuItem("Insert Element");
        MenuItem showContent = new MenuItem("Show content");
        MenuItem primaryIndex = new MenuItem("Create index on primary key");
        MenuItem queryOnTable = new MenuItem("Query on table");

        tableMenu.getItems().add(addAttrItem);
        tableMenu.getItems().add(addRefItem);
        tableMenu.getItems().add(delTableItem);
        tableMenu.getItems().add(insertItem);
        tableMenu.getItems().add(showContent);
        tableMenu.getItems().add(primaryIndex);
        tableMenu.getItems().add(queryOnTable);
        addAttrItem.setOnAction(new EventHandler() {
            public void handle(Event t) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                VBox dialogVbox = new VBox(20);
                dialogVbox.getChildren().add(new Label("Attribute name:"));
                final TextField attrName = new TextField();
                attrName.setMaxWidth(100);
                dialogVbox.getChildren().add(attrName);

                dialogVbox.getChildren().add(new Label("Attribute type:"));
                final ComboBox typeCombo = new ComboBox();
                List<String> list = new ArrayList<String>();
                list.add("String");
                list.add("char");
                list.add("short");
                list.add("int");
                ObservableList<String> observableList = FXCollections.observableList(list);
                typeCombo.setItems(observableList);
                dialogVbox.getChildren().add(typeCombo);

                dialogVbox.getChildren().add(new Label("Length:"));
                final TextField attrLength = new TextField();
                attrLength.setMaxWidth(90);
                dialogVbox.getChildren().add(attrLength);

                dialogVbox.getChildren().add(new Label("Is null:"));
                final ComboBox nullCombo = new ComboBox();
                List<String> listNull = new ArrayList<String>();
                listNull.add("false");
                listNull.add("true");
                ObservableList<String> observableListNull = FXCollections.observableList(listNull);
                nullCombo.setItems(observableListNull);
                dialogVbox.getChildren().add(nullCombo);

                dialogVbox.getChildren().add(new Label("Is primary key:"));
                final ComboBox primaryCombo = new ComboBox();
                List<String> listPrimary = new ArrayList<String>();
                listPrimary.add("false");
                listPrimary.add("true");
                ObservableList<String> observableListPrimary = FXCollections.observableList(listPrimary);
                primaryCombo.setItems(observableListPrimary);
                dialogVbox.getChildren().add(primaryCombo);

                dialogVbox.getChildren().add(new Label("Is unique:"));
                final ComboBox uniqueCombo = new ComboBox();
                List<String> listUnique = new ArrayList<String>();
                listUnique.add("false");
                listUnique.add("true");
                ObservableList<String> observableListUnique = FXCollections.observableList(listUnique);
                uniqueCombo.setItems(observableListUnique);
                dialogVbox.getChildren().add(uniqueCombo);

                Button saveButton = new Button("Done");
                dialogVbox.getChildren().add(saveButton);
                Scene dialogScene = new Scene(dialogVbox, 120, 650);
                dialog.setScene(dialogScene);
                dialog.show();
                saveButton.setOnAction(new EventHandler() {

                    @Override
                    public void handle(Event t) {
                        Attribute attr = new Attribute(attrName.getText(), typeCombo.getValue().toString(),
                                Integer.parseInt(attrLength.getText()), Boolean.parseBoolean(nullCombo.getValue().toString()),
                                Boolean.parseBoolean(uniqueCombo.getValue().toString()));
                        Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                        Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                        if (Boolean.parseBoolean(primaryCombo.getValue().toString())) {
                            table.addPrimaryKey(attr);
                        } else {
                            table.addAttribute(attr);
                        }
                        TreeItem attrItem
                                = new TreeItem(attr, new ImageView(attrIcon));
                        getTreeItem().getChildren().add(attrItem);

                        dialog.close();
                    }

                });
            }
        });
        addRefItem.setOnAction(new EventHandler() {
            public void handle(Event t) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                VBox dialogVbox = new VBox(20);

                dialogVbox.getChildren().add(new Label("Reference to table:"));
                final ComboBox referenceCombo = new ComboBox();
                Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                List<String> tableList = db.getTablesName();
                tableList.remove(getTreeItem().getValue().toString());
                tableList.add(0, "none");
                ObservableList<String> observabletableList = FXCollections.observableList(tableList);
                referenceCombo.setItems(observabletableList);
                dialogVbox.getChildren().add(referenceCombo);

                dialogVbox.getChildren().add(new Label("Reference to attribute:"));
                final ComboBox refAttrCombo = new ComboBox();
                dialogVbox.getChildren().add(refAttrCombo);

                referenceCombo.setOnAction(new EventHandler() {
                    @Override
                    public void handle(Event t) {
                        Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                        Table table = Util.getTableByName(db.getTables(), referenceCombo.getValue().toString());
                        List<String> attrList = table.getPrimaryKeysName();
                        ObservableList<String> observableattrList = FXCollections.observableList(attrList);
                        refAttrCombo.setItems(observableattrList);
                    }
                });

                dialogVbox.getChildren().add(new Label("Is unique:"));
                final ComboBox uniqueCombo = new ComboBox();
                List<String> listUnique = new ArrayList<String>();
                listUnique.add("false");
                listUnique.add("true");
                ObservableList<String> observableListUnique = FXCollections.observableList(listUnique);
                uniqueCombo.setItems(observableListUnique);
                dialogVbox.getChildren().add(uniqueCombo);

                Button saveButton = new Button("Done");
                dialogVbox.getChildren().add(saveButton);
                Scene dialogScene = new Scene(dialogVbox, 120, 420);
                dialog.setScene(dialogScene);
                dialog.show();
                saveButton.setOnAction(new EventHandler() {

                    @Override
                    public void handle(Event t) {
                        Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                        Table reftable = Util.getTableByName(db.getTables(), referenceCombo.getValue().toString());
                        Attribute refattr = Util.getAttributeByName(reftable.getPrimaryKeys(), refAttrCombo.getValue().toString());
                        ForeignKey fk = new ForeignKey(refattr, reftable,
                                Boolean.parseBoolean(uniqueCombo.getValue().toString()));
                        Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                        table.addForeignKey(fk);
                        TreeItem attrItem
                                = new TreeItem(fk, new ImageView(attrIcon));
                        getTreeItem().getChildren().add(attrItem);
                        dialog.close();
                    }

                });
            }
        });
        delTableItem.setOnAction(new EventHandler() {

            @Override
            public void handle(Event t) {
                Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                db.removeTable(table);
                MongoDB.setDatastore(db);
                MongoDB.deleteCollection(table);
                getTreeItem().getParent().getChildren().remove(getTreeItem());
            }
        });
        insertItem.setOnAction(new EventHandler() {
            public void handle(Event t) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                VBox dialogVbox = new VBox(20);

                final Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                final Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                List<Attribute> primaryKeys = table.getPrimaryKeys();

                final List<TextField> textFields = new ArrayList<TextField>();

                for (int i = 0; i < primaryKeys.size(); i++) {
                    Label label = new Label(primaryKeys.get(i).getName());
                    final TextField textField = new TextField();
                    textField.setMaxWidth(100);
                    textFields.add(textField);
                    dialogVbox.getChildren().add(label);
                    dialogVbox.getChildren().add(textField);
                }
                List<Attribute> attributes = table.getAttributes();
                for (int i = 0; i < attributes.size(); i++) {
                    Label label = new Label(attributes.get(i).getName());
                    final TextField textField = new TextField();
                    textField.setMaxWidth(100);
                    textFields.add(textField);
                    dialogVbox.getChildren().add(label);
                    dialogVbox.getChildren().add(textField);
                }
                List<ForeignKey> foreignKeys = table.getForeignKeys();
                for (int i = 0; i < foreignKeys.size(); i++) {
                    Label label = new Label(foreignKeys.get(i).getName());
                    final TextField textField = new TextField();
                    textField.setMaxWidth(100);
                    textFields.add(textField);
                    dialogVbox.getChildren().add(label);
                    dialogVbox.getChildren().add(textField);
                }

                Button saveButton = new Button("Insert");
                dialogVbox.getChildren().add(saveButton);
                Scene dialogScene = new Scene(dialogVbox, 120, 350);
                dialog.setScene(dialogScene);
                dialog.show();
                saveButton.setOnAction(new EventHandler() {

                    @Override
                    public void handle(Event t) {
                        List<String> row = new ArrayList<String>();
                        for (int i = 0; i < textFields.size(); i++) {
                            row.add(textFields.get(i).getText());
                        }
                        MongoDB.setDatastore(db);
                        String err = table.checkRowBeforeInsert(row);
                        if (err != null) {
                            final Stage errDialog = new Stage();
                            errDialog.initModality(Modality.WINDOW_MODAL);
                            VBox errDialogVbox = new VBox(10);
                            errDialogVbox.getChildren().add(new Label(err));
                            Button okButton = new Button("Ok");
                            errDialogVbox.getChildren().add(okButton);
                            Scene errDialogScene = new Scene(errDialogVbox, 120, 80);
                            errDialog.setScene(errDialogScene);
                            errDialog.show();
                            okButton.setOnAction(new EventHandler() {
                                @Override
                                public void handle(Event t) {
                                    errDialog.close();
                                }
                            });
                        } else {
                            MongoDB.saveCollection(table, row);
                            dialog.close();
                        }
                    }
                });
            }
        });
        
        showContent.setOnAction(new EventHandler() {

            @Override
            public void handle(Event event) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                VBox dialogVbox = new VBox(20);
                final Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                MongoDB.setDatastore(db);
                final Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                Button button = new Button("Delete selected row");
                dialogVbox.getChildren().add(button);
                final TableView tableView = DBTableView.createTable(table);
                dialogVbox.getChildren().add(tableView);
                button.setOnAction(new EventHandler() {

                    @Override
                    public void handle(Event t) {

                        ObservableList<String> row = (ObservableList<String>) tableView.getItems().get(tableView.getSelectionModel().getSelectedIndex());
                        int i = 0;
                        String id = MongoDB.construct(table.getPrimaryKeys(), row);
                        String err = db.checkForeignKeyReference(table, row);
                        if(err != null) {
                            final Stage errDialog = new Stage();
                            errDialog.initModality(Modality.WINDOW_MODAL);
                            VBox errDialogVbox = new VBox(10);
                            errDialogVbox.getChildren().add(new Label(err));
                            Button okButton = new Button("Ok");
                            errDialogVbox.getChildren().add(okButton);
                            Scene errDialogScene = new Scene(errDialogVbox, 120, 80);
                            errDialog.setScene(errDialogScene);
                            errDialog.show();
                            okButton.setOnAction(new EventHandler() {
                                @Override
                                public void handle(Event t) {
                                    errDialog.close();
                                }
                            });
                        }
                        else {
                            MongoDB.setDatastore(db);
                            MongoDB.deleteRecord(table, id);
                            table.deleteRowFromIndex(row);
                            tableView.getItems().remove(tableView.getSelectionModel().getSelectedItem());
                        }
                    }
                });

                Scene dialogScene = new Scene(dialogVbox, 520, 350);
                dialog.setScene(dialogScene);
                dialog.show();
            }
        });
        primaryIndex.setOnAction(new EventHandler() {

            @Override
            public void handle(Event event) {
                final Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                MongoDB.setDatastore(db);
                final Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                MongoDB.createIndexOnPrimaryKey(table.getName(), ORD);
                for(Attribute atr : table.getPrimaryKeys())
                    table.addIndexedAttributes(atr);
            }
        });
        
        queryOnTable.setOnAction(new EventHandler() {

            @Override
            public void handle(Event event) {
                final Stage dialog = new Stage();
                dialog.initModality(Modality.APPLICATION_MODAL);
                final VBox dialogVbox = new VBox(20);
                final Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getValue().toString());
                final Table table = Util.getTableByName(db.getTables(), getTreeItem().getValue().toString());
                
                GridPane grid = new GridPane();
                grid.setPadding(new Insets(5));
                grid.setHgap(5);
                grid.setVgap(5);
                
                final List<CheckBox> columns = new ArrayList<>();
                final List<ToggleGroup> selectedSign = new ArrayList<>();
                final List<TextField> condition = new ArrayList<>();
                
                int i = 0;
                grid.add(new Label("SELECT"), 0, i);
                grid.add(new Label("WHERE CONDITION"), 7, i);
                i++;
                
                i = buildGridPane(grid, i, table.getPrimaryKeys(), columns, selectedSign, condition);
                i = buildGridPane(grid, i, table.getAttributes(), columns, selectedSign, condition);
                i = buildGridPane(grid, i, table.getForeignKeysRef(), columns, selectedSign, condition);
                
                dialogVbox.getChildren().add(grid);
                
                GridPane grid2 = new GridPane();
                grid2.setPadding(new Insets(5));
                grid2.setHgap(5);
                grid2.setVgap(5);
                
                i = 0;
                grid2.add(new Label("ORDER BY:"),0,i++);
                i++;
                final ComboBox attrCombo = new ComboBox();                
                List<String> attrList = table.getAllAttributesName();
                attrList.add(0, "none");
                ObservableList<String> observabletableList = FXCollections.observableList(attrList);
                attrCombo.setItems(observabletableList);
                grid2.add(attrCombo,0,i);
                
                final ToggleGroup group = new ToggleGroup();
                RadioButton asc = new RadioButton("ASC");
                asc.setToggleGroup(group);
                asc.setSelected(true);
                grid2.add(asc,1,i);
                RadioButton desc = new RadioButton("DESC");
                desc.setToggleGroup(group);
                grid2.add(desc,2,i);
                i++;
                
                i++;
                grid2.add(new Label("SELECT"),0,i++);
                i++;
                
                final ComboBox attrComboGR = new ComboBox();
                attrComboGR.setItems(observabletableList);
                grid2.add(new Label("gr_attribute:"),0,i);
                grid2.add(attrComboGR,1,i);
                i++;
                
                final ComboBox comboFUNC = new ComboBox(); 
                List<String> funcList = new ArrayList();
                funcList.add("SUM");
                funcList.add("COUNT");
                funcList.add("AVG");
                funcList.add("none");
                ObservableList<String> observabletableListFunc = FXCollections.observableList(funcList);
                comboFUNC.setItems(observabletableListFunc);
                grid2.add(new Label("function:"),0,i);
                grid2.add(comboFUNC,1,i);
                
                final ComboBox attrComboHA = new ComboBox();
                attrComboHA.setItems(observabletableList);
                grid2.add(new Label("having_attr:"),2,i);
                grid2.add(attrComboHA,3,i);
                i++;
                
                i++;
                grid2.add(new Label("GROUP BY:"),0,i);
                grid2.add(new Label("gr_attribute:"),1,i++);
                i++;
                
                grid2.add(new Label("HAVING:"),0,i);
                
                grid2.add(new Label("function "),1,i);
                grid2.add(new Label("having_attr"),2,i);
                
                final ComboBox ComboHavingFunc = new ComboBox();
                List<String> hFuncList = new ArrayList();
                hFuncList.add("=");
                hFuncList.add("!=");
                hFuncList.add("<");
                hFuncList.add("<=");
                hFuncList.add(">");
                hFuncList.add(">=");
                hFuncList.add("none");
                ObservableList<String> observabletableListHFunc = FXCollections.observableList(hFuncList);            
                ComboHavingFunc.setItems(observabletableListHFunc);
                grid2.add(ComboHavingFunc,3,i);
         
                final TextField havingVal = new TextField("");
                grid2.add(havingVal,4,i);
                i++;
                
                dialogVbox.getChildren().add(grid2);
                
                Button button = new Button("Show result");
                dialogVbox.getChildren().add(button);
                                
                button.setOnAction(new EventHandler() {
                    @Override
                    public void handle(Event t) {
                        
                        String err = null;
                        
                        List<Condition> conditions = new ArrayList();
                        System.out.println("Conditions:");
                        for(int i=0; i<columns.size(); i++) {
                            RadioButton chk = (RadioButton)selectedSign.get(i).getSelectedToggle();
                            if(chk != null) {
                                System.out.println(Util.getAttributeByName(table, columns.get(i).getText()).getName());
                                System.out.println(chk.getText());
                                System.out.println(condition.get(i).getText());
                                if(condition.get(i).getText().equals("")) {
                                    err = "You must set the right side of the condition for "+Util.getAttributeByName(table, columns.get(i).getText()).getName();
                                } else {
                                    conditions.add(new Condition(Util.getAttributeByName(table, columns.get(i).getText()),
                                        chk.getText(), condition.get(i).getText()));  
                                }
                            }
                        }

                        if(err != null) {
                            errorBox(err);
                        }
                        else {
                            List<Attribute> wantedAttrs = new ArrayList();
                            for(CheckBox cb: columns) {
                                if(cb.isSelected()) {
                                    wantedAttrs.add(Util.getAttributeByName(table, cb.getText()));
                                }
                            }
                            
                            Attribute forOrderBy = null;
                            Attribute forGroupBy = null;
                            Attribute forHaving = null;
                            
                            List<Attribute> notNeeded = new ArrayList();
                            
                            //if there is an order by condition, add the attribute to the list
                            if(attrCombo.getValue()!=null && !attrCombo.getValue().toString().equals("none")) {
                                forOrderBy = Util.getAttributeByName(table, attrCombo.getValue().toString());
                                if(!wantedAttrs.contains(forOrderBy)) {
                                    wantedAttrs.add(forOrderBy);
                                    notNeeded.add(forOrderBy);
                                }
                            }
                            if(attrComboGR.getValue()!=null && !attrComboGR.getValue().toString().equals("none")) {
                                forGroupBy = Util.getAttributeByName(table, attrComboGR.getValue().toString());
                                if(!wantedAttrs.contains(forGroupBy)) {
                                    wantedAttrs.add(forGroupBy);
                                    notNeeded.add(forGroupBy);
                                }
                            }
                            if(attrComboHA.getValue()!=null && !attrComboHA.getValue().toString().equals("none")) {
                                forHaving = Util.getAttributeByName(table, attrComboHA.getValue().toString());
                                if(!wantedAttrs.contains(forHaving)) {
                                    wantedAttrs.add(forHaving);
                                    notNeeded.add(forHaving);
                                }
                            }
                       
                            MongoDB.setDatastore(db);
                            List<List<String>> values = Selection.getMatchingRows(table, conditions, wantedAttrs);
                            for(int i = 0; i<values.size(); i++){
                                System.out.println("value "+i+":");
                                for(int j = 0; j<values.get(i).size(); j++) {
                                    System.out.println(values.get(i).get(j));
                                }
                            }
                            
                            if(forOrderBy!=null) {
                                RadioButton rb = (RadioButton)group.getSelectedToggle();
                                if(rb!=null) {
                                    Table tableTemp = new Table(table.getName());
                                    tableTemp.setAttributes(wantedAttrs);
                                    values = Selection.oderBy(values, tableTemp, forOrderBy, rb.getText());
                                }
                            }
                            
                            if(forGroupBy!=null && forHaving!=null && forGroupBy!=null && comboFUNC.getValue()!=null && comboFUNC.getValue()!="none") {
                                Table tableTemp = new Table(table.getName());
                                tableTemp.setAttributes(wantedAttrs);
                                String functionH = comboFUNC.getValue().toString();
                                String havingAttrName = null;
                                String havingOp = null;
                                String havingValue = null; 
                                if(ComboHavingFunc.getValue()!=null && ComboHavingFunc.getValue()!="none") {
                                    havingOp = ComboHavingFunc.getValue().toString();
                                    havingValue = havingVal.getText();
                                    havingAttrName = functionH;
                                    System.out.println(havingOp);
                                    System.out.println(havingValue);
                                }
                                System.out.println(forGroupBy.getName());
                                System.out.println(forHaving.getName());
                                System.out.println(functionH);
                                System.out.println(havingAttrName);
                                values = Selection.groupBy(values, tableTemp, forGroupBy, forHaving, functionH, havingAttrName, havingOp, havingValue);   
                                System.out.println("Values from group by");
                                for(int k=0; k<values.size(); k++)
                                    for(int j=0; j<values.get(k).size(); j++)
                                        System.out.println(values.get(k).get(j));
                                wantedAttrs.clear();
                                wantedAttrs.add(forGroupBy);
                                if(!forGroupBy.getName().equals(forHaving.getName()))
                                    wantedAttrs.add(forHaving);
                                notNeeded = null;
                            }
                        
                            if (dialogVbox.getChildren().size() > 3) {
                                dialogVbox.getChildren().remove(3);
                            }
                            if(values != null)
                                dialogVbox.getChildren().add(3, DBTableView.createTable(wantedAttrs, values, notNeeded));
                            else 
                                errorBox("Can not reproduce!");
                        }
                    }
                });

                Scene dialogScene = new Scene(dialogVbox, 600, 400);
                dialog.setScene(dialogScene);
                dialog.show();
            }
        });
    }

    private void createAttrMenu() {
        MenuItem delAttrItem = new MenuItem("Delete Attribute");
        MenuItem createIndex = new MenuItem("Create index");
        attrMenu.getItems().add(delAttrItem);
        attrMenu.getItems().add(createIndex);

        delAttrItem.setOnAction(new EventHandler() {
            @Override
            public void handle(Event t) {
                Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getParent().getValue().toString());
                Table table = Util.getTableByName(db.getTables(), getTreeItem().getParent().getValue().toString());
                String[] attrparam = getTreeItem().getValue().toString().split(":");
                Attribute attr = Util.getAttributeByName(table.getAttributes(), attrparam[0]);
                if (attr != null) {
                    table.removeAttribute(attr);
                } else {
                    attr = Util.getAttributeByName(table.getPrimaryKeys(), attrparam[0]);
                    if (attr != null) {
                        table.removePrimaryKey(attr);
                    } else {
                        table.removeForeignKey(Util.getForeignKeyByName(table.getForeignKeys(), attrparam[0]));
                    }
                }
                table.removeIndexedAttributes(attr);
                getTreeItem().getParent().getChildren().remove(getTreeItem());
            }
        });
        
        createIndex.setOnAction(new EventHandler() {

            @Override
            public void handle(Event event) {
                Database db = Util.getDBByName(dbs.getDatabases(), getTreeItem().getParent().getParent().getValue().toString());
                Table table = Util.getTableByName(db.getTables(), getTreeItem().getParent().getValue().toString());
                MongoDB.setDatastore(db);
                String[] attrparam = getTreeItem().getValue().toString().split(":");
                Attribute attr = Util.getAttributeByName(table.getAttributes(), attrparam[0]);
                if (attr == null) {
                    ForeignKey fattr = Util.getForeignKeyByName(table.getForeignKeys(), attrparam[0]);
                    if(fattr != null) {
                        if(fattr.isUnique())
                            MongoDB.createIndexOnUniqueKey(table, fattr);
                        else
                            MongoDB.createIndexOnNonUniqueKey(table, fattr);
                        table.addIndexedAttributes(fattr);
                    } 
                } else {
                    if(attr.isUnique())
                        MongoDB.createIndexOnUniqueKey(table, attr);
                    else {
                        MongoDB.createIndexOnNonUniqueKey(table, attr);
                    }
                    table.addIndexedAttributes(attr);
                }
            }     
        });
    }

    @Override
    public void updateItem(Object item, boolean empty) {
        super.updateItem(item, empty);

        if (empty) {
            setText(null);
            setGraphic(null);
        } else {
            if (isEditing()) {
                if (textField != null) {
                    textField.setText(getString());
                }
                setText(null);
                setGraphic(textField);
            } else {
                setText(getString());
                setGraphic(getTreeItem().getGraphic());
                //if (!getTreeItem().isLeaf() && getTreeItem().getParent() != null) {

                String type = ((Model) item).getType();
                if (type.equals("DBS")) {
                    setContextMenu(dbsMenu);
                } else if (type.equals("DB")) {
                    setContextMenu(dbMenu);
                } else if (type.equals("TABLE")) {
                    setContextMenu(tableMenu);
                } else if (type.equals("ATTR")) {
                    setContextMenu(attrMenu);
                }
            }
        }
    }

    private String getString() {
        return getItem() == null ? "" : getItem().toString();
    }
    
    private int buildGridPane(GridPane pane, int i, List<Attribute> attributes, List<CheckBox> columns, List<ToggleGroup> selectedSign, List<TextField> condition) {
        for (Attribute attr: attributes){
            CheckBox cb = new CheckBox(attr.getName());
            columns.add(cb);
                    
            final ToggleGroup group = new ToggleGroup();
            selectedSign.add(group);
            
            RadioButton rbe = new RadioButton("=");
            rbe.setToggleGroup(group);
            RadioButton rbne = new RadioButton("!=");
            rbne.setToggleGroup(group);
            RadioButton rbg = new RadioButton(">");
            rbg.setToggleGroup(group);
            RadioButton rbge = new RadioButton(">=");
            rbge.setToggleGroup(group);
            RadioButton rbl = new RadioButton("<");
            rbl.setToggleGroup(group);
            RadioButton rble = new RadioButton("<=");
            rble.setToggleGroup(group);
                    
            final TextField t = new TextField("");
            condition.add(t);
            
            Button b = new Button("rem.cond.");
            b.setOnAction(new EventHandler() {
                @Override
                public void handle(Event e) {
                    group.getSelectedToggle().setSelected(false);
                    t.setText("");
                }
            });
                    
            pane.add(cb, 0, i);
            pane.add(rbe, 1, i);
            pane.add(rbne, 2, i);
            pane.add(rbg, 3, i);
            pane.add(rbge, 4, i);
            pane.add(rbl, 5, i);
            pane.add(rble, 6, i);
            pane.add(t, 7, i);
            pane.add(b, 8, i);
            i++;
        }
        return i;
    }
    
    private void errorBox(String err) {
        final Stage errDialog = new Stage();
        errDialog.initModality(Modality.WINDOW_MODAL);
        VBox errDialogVbox = new VBox(10);
        errDialogVbox.getChildren().add(new Label(err));
        Button okButton = new Button("Ok");
        errDialogVbox.getChildren().add(okButton);
        Scene errDialogScene = new Scene(errDialogVbox, 120, 80);
        errDialog.setScene(errDialogScene);
        errDialog.show();
        okButton.setOnAction(new EventHandler() {
            @Override
            public void handle(Event t) {
                errDialog.close();
            }
        });
    }

}
